create database Student
go

use Student
go

create table class_info
(
	class_id int constraint pk_class_id primary key identity,
	class_name nvarchar(20) unique not null
)

create table student_info
(
	stu_id int primary key identity,
	class_id int constraint fk_class_id foreign key references class_info(class_id),
	stu_name nvarchar(20) not null,
	stu_sex nvarchar(1) check(stu_sex='' or stu_sex='Ů'),
	stu_birthday date,
	stu_phone nvarchar(11) unique
)

alter table student_info
add stu_address nvarchar(200)

create table course
(
	course_id int identity primary key,
	course_name nvarchar(50) unique not null,
	course_credit int not null default(1) check(course_credit>=1 and course_credit<=5)
)

create table score
(
	score_id int identity,
	stu_id int,
	course_id int,
	score decimal(5, 2) unique not null
)

alter table score
add constraint pk_socre_id primary key (score_id)

alter table score
add constraint fk_stu_id foreign key (stu_id) references student_info (stu_id)

alter table score
add constraint fk_class2_id foreign key (stu_id) references course (course_id)

